package com.ustcinfo.study.worker1.r2;


import java.sql.*;

/**
 * Describe:  hive连接，并执行HiveSql
 * Author: yang.yang
 * Mail: yang.yang2@ustcinfo.com
 * Date: 2018年08月03日 20:03
 */
public class HiveTest {



    // hive链接地址
    private static String url = "jdbc:hive2://nodex3:10000/default";

    //hive用户名
    private static String userName = "root";

    // hive密码
    private static String pwd = " ";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询tables sql
    private static String showTableSql = "show tables";

    // 创建内部表cityDns sql
    private static String createSql = "create table cityDns(ipMin string,ipMax string,domain_name string)row format delimited fields terminated by '\t' ";
    // 加载数据到cityDns sql
    private static String localDataSql = "load data local inpath '/usr/city_dns1.txt' into table cityDns ";

    // 查询出cityDns表中域名个数
    private static String countSql = "select * from citydns  ";
    //删除cityDns表
    private static  String deletSql="drop table cityDns";
    //删除cityDns空行
    private static  String deletSqlRow="insert overwrite table citydns select * from citydns where domain_name != ''";
    //创建dns_ip1存放日志文件domain_name ，ip
    private static String sql_create_table1 = "create table dns_ip1( domain_name String,ip String) row format delimited fields terminated by '|'";
   //将日志文件domain_name ，ip写入dns_ip1
    private static String sql_data = "insert overwrite table dns_ip1 select ip,domain_name from dns_ip";
    //创建日志文件表dns_ip
    private static String sql_create_table = "create table dns_ip(" +
            "ip String,domain_name String, access_time String, target_ip String,status String)" +
            "row format delimited fields terminated by '|'";
    //加载日志文件到dns_ip
    private static String sql_load_file = "load data local inpath '/usr/100_1_20170207160030' into table dns_ip";
    //查询日志前一百行
    private static String sql_data1 = "select domain_name,ip from dns_ip limit 100";
    //创建保存结果的表
    private static String createSql1 = "create table resultDns(cityName String ,domain_name String,ipsize int)row format delimited fields terminated by ' ' ";
    //结果查询
    private static String sqlResult="insert overwrite table resultDns SELECT * from view7 as a where 3>(select count(*) from view7 WHERE cityName= a.cityName  and ipsize > a.ipsize) ORDER BY cityName, a.ipsize DESC";
    //创建view
    private static String sqlView="create  view view5 as select ip,domain_name,count(ip) as ipsize from dns_ip1 group by ip,domain_name";
    private static String sqlView1="create view view7 as select citydns.domain_name as cityName,view5.domain_name,ipsize from citydns,view5 WHERE view5.ip<citydns.ipMax and view5.ip > citydns.ipMin ";
//    //结果查询
//    private static  String sqlResult="SELECT * from view7 limit 100";


    public static void main(String[] args) throws SQLException {

        //  加载hive驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }
        try {
            // 连接hive
            connection = DriverManager.getConnection(url, userName, pwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }
            statement = connection.createStatement();

            statement.execute(createSql);
            statement.execute(localDataSql);
            statement.execute(deletSqlRow);
            statement.execute(sql_create_table);
            statement.execute(sql_create_table1);
            statement.execute(sql_load_file);
            statement.execute(sql_data);
            statement.execute(sqlView);
            statement.execute(sqlView1);
            statement.execute(createSql1);
            resultSet= statement.executeQuery(sqlResult);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1)+resultSet.getString(2)+resultSet.getString(3));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }


    }}
